﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using Sw.Hospital.HealthExaminationSystem.Common.Bases;
using System.Data.OleDb;
using Sw.Hospital.HealthExaminationSystem.Application.CusReg.Dto;
using Sw.Hospital.HealthExaminationSystem.Common.Helpers;
using Sw.Hospital.HealthExaminationSystem.Common.Enums;
using Sw.Hospital.HealthExaminationSystem.Application.Company.Dto;
using Sw.Hospital.HealthExaminationSystem.Application.Company;
using Sw.Hospital.HealthExaminationSystem.Application.Department.Dto;
using Sw.Hospital.HealthExaminationSystem.Application.CusReg;
using Sw.Hospital.HealthExaminationSystem.ApiProxy.CusReg;
using DevExpress.Utils;
using Sw.His.Common.Functional.Unit.CustomTools;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using Sw.Hospital.HealthExaminationSystem.Application.IDNumber;
using Sw.Hospital.HealthExaminationSystem.ApiProxy;
using Newtonsoft.Json;
using Abp.Application.Services.Dto;
using HealthExaminationSystem.Enumerations;
using HealthExaminationSystem.Enumerations.Helpers;
using Sw.Hospital.HealthExaminationSystem.Common.UserCache;
using Sw.Hospital.HealthExaminationSystem.Application.Charge;
using NPOI.POIFS.FileSystem;
using NPOI;
using Sw.Hospital.HealthExaminationSystem.Application.Charge.Dto;
using Sw.Hospital.HealthExaminationSystem.Application.OccDisProposalNew;
using Sw.Hospital.HealthExaminationSystem.Application.OccHazardFactor;
using Sw.Hospital.HealthExaminationSystem.ApiProxy.OccHazardFactor;
using Sw.Hospital.HealthExaminationSystem.Application.OccDisProposalNew.Dto;
using Sw.Hospital.HealthExaminationSystem.Application.ItemGroup.Dto;
using Sw.Hospital.HealthExaminationSystem.Application.BasicDictionary.Dto;

namespace Sw.Hospital.HealthExaminationSystem.Company
{
    public partial class FrmImportExcel : UserBaseForm
    {
        #region 构造

        private readonly IClientRegAppService _service = new ClientRegAppService();
        private ICustomerAppService customerSvr = new CustomerAppService();//体检预约
        private List<TbmDepartmentDto> Departments;//科室数据

        public Guid ClientRegId;//单位预约ID
        private FrmClientRegCustomerList frmCliRegCustomer;
        //编码
        IIDNumberAppService _iIdNumber = new IDNumberAppService();
        private IWorkbook _workbook;

        public ClientTeamRegDto cteDto;  //单位团体预约Dto
        private IChargeAppService _chargeAppService { get; set; }

        private IIDNumberAppService iIDNumberAppService;

        private readonly TjlCustomerRegDto _customerRegRepository; //人员预约信息
        private readonly ICustomerAppService _customerRegRepositorys; //人员预约信息
        public readonly IOccDisProposalNewAppService _IOccDisProposalNewAppService;
        private readonly IOccHazardFactorAppService _OccHazardFactorAppService;
        //public List<CreateClientTeamInfoesDto> ListClientTeam; //单位预约分组信息

        //public List<ClientTeamRegitemViewDto> ListClientTeamItem; //单位预约分组项目信息

        List<SimpleItemGroupDto> CacheSimpleItemGroups = new List<SimpleItemGroupDto>();
        List<ShowOccDictionary> showOccDictionarys = new List<ShowOccDictionary>();
        List<BasicDictionaryDto> CacheBasicDictionarys = new List<BasicDictionaryDto>();
        List<TbmDepartmentDto> CacheDepartmentDtos = new List<TbmDepartmentDto>();
        #endregion

        public FrmImportExcel()
        {
            InitializeComponent();
            excelDs.Fill();
            _OccHazardFactorAppService = new OccHazardFactorAppService();
            _IOccDisProposalNewAppService = new OccDisProposalNewAppService();
            _chargeAppService = new ChargeAppService();
            iIDNumberAppService = new IDNumberAppService();
            //预检时间
            //gdvImportExcel.Columns[col预检时间.FieldName].DisplayFormat.FormatType = FormatType.Custom;
            //gdvImportExcel.Columns[col预检时间.FieldName].DisplayFormat.Format = new CustomFormatter(FormatDatetime);

            //出生日期
            //gdvImportExcel.Columns[col出生日期.FieldName].DisplayFormat.FormatType = FormatType.Custom;
            //gdvImportExcel.Columns[col出生日期.FieldName].DisplayFormat.Format = new CustomFormatter(FormatDatetime);
            // This line of code is generated by Data Source Configuration Wizard
            // Fill a ExcelDataSource
            //excelDs.Fill();
        }
        public FrmImportExcel(FrmClientRegCustomerList _frm)
        {
            InitializeComponent();
            excelDs.Fill();
            frmCliRegCustomer = _frm;

            //预检时间
            gdvImportExcel.Columns[col预检时间.FieldName].DisplayFormat.FormatType = FormatType.Custom;
            gdvImportExcel.Columns[col预检时间.FieldName].DisplayFormat.Format = new CustomFormatter(FormatDatetime);

            //出生日期
            gdvImportExcel.Columns[col出生日期.FieldName].DisplayFormat.FormatType = FormatType.Custom;
            gdvImportExcel.Columns[col出生日期.FieldName].DisplayFormat.Format = new CustomFormatter(FormatDatetime);
        }

        /// <summary>
        /// 时间转换
        /// </summary>
        /// <param name="arg"></param>
        /// <returns></returns>
        private string FormatDatetime(object arg)
        {
            try
            {
                return DateTime.FromOADate(Convert.ToInt32(arg)).ToString();

                //return _sexModels.Find(r => r.Id == (int)arg).Display;
            }
            catch (Exception)
            {
                return null;
                //return _sexModels.Find(r => r.Id == (int)Sex.GenderNotSpecified).Display;
            }
        }

        #region 系统事件

        /// <summary>
        /// 选择文件导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOpen_Click(object sender, EventArgs e)
        {
            if (getFZState())
            {
                ShowMessageBoxInformation("单位已封账,不能进行收费设置！");
                return;
            }
            Import();
        }

        /// <summary>
        /// 导入数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImport_Click(object sender, EventArgs e)
        {
            if (getFZState())
            {
                ShowMessageBoxInformation("单位已封账,不能进行收费设置！");
                return;
            }
            if (ImportDataSouceNew())
            {
                DialogResult = DialogResult.OK;
                //ShowMessageSucceed("导入成功！");
            }
        }


        /// <summary>
        /// grid样式展示
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void gdvImportExcel_RowCellStyle(object sender, DevExpress.XtraGrid.Views.Grid.RowCellStyleEventArgs e)
        {
            if (e.RowHandle < 0)
                return;
            var isadd = gdvImportExcel.GetRowCellValue(e.RowHandle, col状态);
            if (isadd == null)
                return;
            if (Convert.ToInt32(isadd) == 1)
            {
                e.Appearance.ForeColor = Color.Red;
            }
            else if (Convert.ToInt32(isadd) == 0)
            {
                e.Appearance.ForeColor = Color.Green;
                //e.Appearance.FontStyleDelta = FontStyle.Strikeout;
            }
        }

        #endregion

        #region 实现方法

        /// <summary>
        /// 选择文件导入
        /// </summary>
        public void Import()
        {
            openFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(*.xlsx)|*.xlsx";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                DataTable dt = new DataTable();
                AutoLoading(() =>
                {
                    dt = ExcelToDataTable(openFileDialog.FileName, "Sheet", true);
                });
                gridControlImportExcel.DataSource = dt;
            }
        }


        /// <summary>
        /// 将 Excel 中的数据导入到 DataTable 中
        /// </summary>
        /// <param name="sheetName">Excel 工作薄 Sheet 的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是 DataTable 的列名</param>
        /// <returns>返回的 DataTable</returns>
        public DataTable ExcelToDataTable(string _fileName, string sheetName, bool isFirstRowColumn)
        {
            //异常数据导出
            var Errordt= new DataTable();
            var data = new DataTable();                    
            FileStream _fs = new FileStream(_fileName, FileMode.Open, FileAccess.Read);

          
            if (POIFSFileSystem.HasPOIFSHeader(_fs))
            {
                _workbook = new HSSFWorkbook(_fs);
            }
            if (_workbook==null && POIXMLDocument.HasOOXMLHeader(_fs))
            {
                _workbook = new XSSFWorkbook(_fs);
            }
            if (_workbook == null)
            { _workbook = new XSSFWorkbook(_fs); }



            ISheet sheet;
            if (sheetName != null)
            {
                // 如果没有找到指定的 SheetName 对应的 Sheet，则尝试获取第一个 Sheet
                sheet = _workbook.GetSheet(sheetName) ?? _workbook.GetSheetAt(0);
            }
            else
            {
                sheet = _workbook.GetSheetAt(0);
            }

            if (sheet != null)
            {
                var firstRow = sheet.GetRow(0);
                // 一行最后一个 Cell 的编号，即总的列数
                int cellCount = firstRow.LastCellNum;
                int startRow;
                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        var cell = firstRow.GetCell(i);
                        var cellValue = cell?.StringCellValue;

                        if (cellValue != null)
                        {
                            var column = new DataColumn(cellValue);
                            data.Columns.Add(column);
                        }
                    }

                    var colText = new DataColumn("导入状态");
                    var colState = new DataColumn("状态");
                    var colTeamName = new DataColumn("分组名称");
                    if (!data.Columns.Contains("导入状态"))
                    {
                        data.Columns.Add(colText);
                    }
                    if (!data.Columns.Contains("状态"))
                    {
                        data.Columns.Add(colState);
                    }
                    if (!data.Columns.Contains("分组名称"))
                    {
                        data.Columns.Add(colTeamName);
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }

                // 最后一列的标号
                var rowCount = sheet.LastRowNum;
                if (rowCount > 0)
                    if (cteDto.ListClientTeam == null || cteDto.ListClientTeam.Count == 0)
                    {
                        cteDto.ListClientTeam = _service.GetClientTeam(new EntityDto<Guid> { Id = ClientRegId });
                    }
                Errordt = data.Clone();
               
                for (var i = startRow; i <= rowCount; ++i)
                {
                    
                    bool boolErr = false;
                    string rowErr = "";
                    var row = sheet.GetRow(i);                  
                    // 没有数据的行默认是 NULL       
                    if (row == null)
                        continue;
                    if (row.FirstCellNum < 0)
                    { continue; }
                    if (row.GetCell(0) == null)
                    {
                        boolErr = true;
                        rowErr += "姓名不能为空！";                       
                    }
                   else if (string.IsNullOrWhiteSpace(row.GetCell(0).ToString()))
                    {
                        boolErr = true;
                        rowErr += "姓名不能为空！";
                    }
                    if (string.IsNullOrEmpty( row.GetCell(1)?.ToString()) && string.IsNullOrEmpty(row.GetCell(7)?.ToString()))
                    {
                        boolErr = true;
                        rowErr += "性别或身份证号不能为空！";
                    }
                   
                    string strage = row.GetCell(2) == null ? "" : row.GetCell(2).ToString();
                    string strIdNum = row.GetCell(7) == null ? "" : row.GetCell(7).ToString();

                    if (string.IsNullOrEmpty(strage) && string.IsNullOrEmpty(strIdNum))
                    {
                        try
                        {
                            if (row.GetCell(8) == null || row.GetCell(8).DateCellValue == new DateTime())
                            {
                               
                                boolErr = true;
                                rowErr += "出生日期,年龄和身份证号必须有一个不能为空！！";
                            }
                        }
                        catch (Exception ex)
                        {
                            
                            boolErr = true;
                            rowErr += "出生日期格式错误";
                        }
                       
                    }
                    var dataRow = data.NewRow();                 
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        if (sheet.GetRow(0).GetCell(j).ToString() == "身份证号")
                        {
                            if (row.GetCell(j) != null && row.GetCell(j).ToString() != "")
                            {
                                //if (row.GetCell(j).ToString() == "320223197605293286")
                                //{
                                //}
                                var dataIDCard = VerificationHelper.GetByIdCard(row.GetCell(j).ToString().Trim());
                                //SearchCustomerDto dto = new SearchCustomerDto();
                                //dto.IDCardNo = row.GetCell(j).ToString();   
                                //var sss = row.GetCell(35)?.ToString();
                                if (dataIDCard != null)
                                {
                                    //  //var team = cteDto.ListClientTeam.FirstOrDefault(o => o.ClientRegId == ClientRegId && o.TeamBM == Convert.ToInt32(dataRow[CustomerTemplate.TeamCode]));
                                    //  var dtos = _customerRegRepositorys.QueryCustomerReg(dto);
                                    //  //var a = _customerRegRepository.Select(o => o.Customer.IDCardNo);
                                    //  //string? b = _customerRegRepository.Where(o => o.Customer.IDCardNo.Contains(dataIDCards)).ToString();
                                    //if (dtos.Where(o => o.Customer.IDCardNo.Contains(dto.IDCardNo)).Count()>0)
                                    // {
                                    //      string er = dataRow["姓名 *"].ToString() + "身份证：" + row.GetCell(j).ToString() + "重复！" + "\r\n";
                                    //      isErr += er;
                                    //      isOK = false;
                                    //      cusIsOK = false;
                                    //      break;
                                    //  }

                                    dataRow["年龄 *"] = dataIDCard.Age;
                                    dataRow["出生日期"] = dataIDCard.Birthday;
                                    dataRow["身份证号"] = row.GetCell(j).ToString().Trim();
                                    dataRow["性别 *"] = SexHelper.CustomSexFormatter((int)dataIDCard.Sex);


                                }
                               
                                else if (row.GetCell(35) != null && !row.GetCell(35).ToString().Contains("身份证"))
                                {
                                    try
                                    {
                                        if (string.IsNullOrEmpty(row.GetCell(1)?.ToString()) )
                                        {
                                            boolErr = true;
                                            rowErr += "性别或身份证号不能为空！";
                                        }                                       

                                        if (string.IsNullOrEmpty(strage) )
                                        {
                                            try
                                            {
                                                if (row.GetCell(8) == null || row.GetCell(8).DateCellValue == new DateTime())
                                                {

                                                    boolErr = true;
                                                    rowErr += "出生日期,年龄和身份证号必须有一个不能为空！！";
                                                }
                                            }
                                            catch (Exception ex)
                                            {

                                                boolErr = true;
                                                rowErr += "出生日期格式错误";
                                            }

                                        }
                                    }
                                    catch (Exception ex)
                                    {

                                        boolErr = true;
                                        rowErr += "出生日期格式错误";
                                    }
                                    dataRow["身份证号"] = row.GetCell(j).ToString().Trim();
                                }
                                else  
                                {


                                    boolErr = true;
                                    rowErr += "身份证号有误,";

                                }
                            }
                        }

                        else if (sheet.GetRow(0).GetCell(j).ToString() == "预检时间")
                        {
                            try
                            {
                                var sss = row.GetCell(j);
                                if (row.GetCell(j) == null)
                                    dataRow[j] = DateTime.Now.ToLongDateString();
                                else
                                {
                                    if (row.GetCell(j).ToString() != "")
                                    {
                                        var ss = row.GetCell(j).DateCellValue.ToString();
                                        dataRow[j] = DateTime.Parse(ss);
                                        //Convert.ToDateTime(ss).ToLongDateString(); 
                                        //row.GetCell(j).DateCellValue.ToLongDateString();                                        
                                    }
                                    else
                                    {
                                        dataRow[j] = DateTime.Now.ToLongDateString();

                                    }

                                }
                            }
                            catch (Exception ex)
                            {
                              
                                boolErr = true;
                                rowErr += "预检时间有误";

                               
                            }
                        }
                        else if (sheet.GetRow(0).GetCell(j).ToString() == "接害开始时间")
                        {
                            try
                            {
                                var sss = row.GetCell(j);
                                if (row.GetCell(j) != null)
                                {
                                    if (row.GetCell(j).ToString() != "")
                                    {
                                        var ss = row.GetCell(j).DateCellValue.ToString();
                                        dataRow[j] = DateTime.Parse(ss);
                                        //Convert.ToDateTime(ss).ToLongDateString(); 
                                        //row.GetCell(j).DateCellValue.ToLongDateString();                                        
                                    }                                

                                }
                            }
                            catch (Exception ex)
                            {

                                boolErr = true;
                                rowErr += "接害开始时间";


                            }
                        }
                        else if (sheet.GetRow(0).GetCell(j).ToString() == "出生日期")
                        {
                            try
                            {
                                 var ss = dataRow["年龄 *"].ToString();
                             
                                 var bir = row.GetCell(j);
                                if (bir == null)
                                { dataRow[j] = DateTime.Now.AddYears(-Convert.ToInt32(dataRow["年龄 *"])); }
                               else if (DateTime.TryParse(bir.DateCellValue.ToString(),out  DateTime daten) && daten == new DateTime())
                                    dataRow[j] = DateTime.Now.AddYears(-Convert.ToInt32(dataRow["年龄 *"]));
                                else
                                {
                                    dataRow[j] = daten;// row.GetCell(j).DateCellValue.ToLongDateString();
                                    if (string.IsNullOrWhiteSpace(dataRow["年龄 *"].ToString()))
                                    {
                                        var age = DateTime.Now.Year - daten.Year;
                                        if (age < 1)
                                            age = 1;
                                        dataRow["年龄 *"] = age;
                                    }
                                }
                            }
                            catch (Exception ex)
                            {                                
                              
                                boolErr = true;
                                rowErr += "出生日期有误!";
                              
                            }
                        }
                        //同理，没有数据的单元格都默认是 NULL
                        else if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString().Trim();
                        }
                        //if (sheet.GetRow(0).GetCell(j).ToString() == "移动电话")
                        //{
                        //    //var yz = "^1[3|4|5|7|8][0-9]{9}$";
                        //    if (row.GetCell(j) != null && row.GetCell(j).ToString() != "")
                        //    {
                        //        var dataIDCard = row.GetCell(j).ToString();
                        //        if (System.Text.RegularExpressions.Regex.IsMatch(dataIDCard, @"^1[3|4|5|7|8][0-9]{9}$"))
                        //        {
                        //            dataRow["移动电话"] = row.GetCell(j).ToString();
                        //        }                                                                   
                        //        else
                        //        {
                        //            string er = dataRow["姓名 *"].ToString() + "移动电话：" + row.GetCell(j).ToString() + "有错误！" + "\r\n";
                        //            // MessageBox.Show(isErr += er);
                        //            isErr += er;
                        //            isOK = false;
                        //            cusIsOK = false;
                        //            break;
                        //            //isErr += er;
                        //            //isOK = false;

                        //            //MessageBox.Show(dataRow["姓名 *"].ToString() + "身份证：" + row.GetCell(j).ToString() + "有错误！");                                    
                        //            return new DataTable();
                        //        }
                        //    }
                        //}
                    }                  
                    if (boolErr == true)
                    {
                        dataRow["状态"] = 1;
                        dataRow["导入状态"] = rowErr;
                    }
                    else
                    {
                        dataRow["状态"] = 0;
                        dataRow["导入状态"] = "通过";
                    }
                    //判断增加分组信息
                    //编码为空时判断
                    if (dataRow["状态"].ToString() =="0")
                    {
                        #region 职业检相关校验
                        //工种
                        if (dataRow["工种"] != null && !string.IsNullOrEmpty(dataRow["工种"].ToString()))
                        { 
                          
                            ChargeBM chargeBM = new ChargeBM();                          
                              
                            //工种
                            chargeBM.Name = ZYBBasicDictionaryType.WorkType.ToString();
                            var lis3 = _IOccDisProposalNewAppService.getOutOccDictionaryDto(chargeBM);
                            var gz = dataRow["工种"].ToString();
                            if (!lis3.Any(p => p.Text == dataRow["工种"].ToString()))
                            {
                                dataRow["状态"] = 1;
                                dataRow["导入状态"] = "工种：\"" + dataRow["工种"].ToString() + "\"系统中不存在不能导入！";
                            }
                        }
                        //工种
                        if (dataRow["危害因素（多个，用“|”隔开）"] != null && !string.IsNullOrEmpty(dataRow["危害因素（多个，用“|”隔开）"].ToString()))
                        {

                            var outresult = _OccHazardFactorAppService.getSimpOccHazardFactors();
                            var rhylist = dataRow["危害因素（多个，用“|”隔开）"].ToString().Split('|').ToList();
                            foreach (var risk in rhylist)
                            {
                                if (!outresult.Any(p => p.Text == risk))
                                {
                                    dataRow["状态"] = 1;
                                    dataRow["导入状态"] = "危害因素：\"" + risk + "\"系统中不存在不能导入！";
                                    break;
                                }
                            }

                        }
                        #endregion

                        if (dataRow[CustomerTemplate.Age] == null)
                            dataRow[CustomerTemplate.Age] = 0;
                        var Age = Convert.ToInt32(dataRow[CustomerTemplate.Age]);
                        var dtSex = SexHelper.GetSexModels().Where(o => o.Display == dataRow[CustomerTemplate.Sex].ToString()).Select(o => o.Id).FirstOrDefault();
                        var MarriageStatus = MarrySateHelper.GetMarrySateModels().Where(o => o.Display == dataRow[CustomerTemplate.MarriageStatus].ToString()).Select(o => o.Id).FirstOrDefault();
                        var ReadyPregnancybirth = BreedStateHelper.GetBreedStateModels().Where(o => o.Display == dataRow[CustomerTemplate.ReadyPregnancybirth].ToString()).Select(o => o.Id).FirstOrDefault();
                        if (string.IsNullOrWhiteSpace(dataRow[CustomerTemplate.TeamCode].ToString()))
                        {

                            var lisTeam = cteDto.ListClientTeam.Where(o => o.MinAge < Age && Age < o.MaxAge);
                            lisTeam = lisTeam.Where(o => o.Sex == dtSex || o.Sex == (int)Sex.GenderNotSpecified);
                            if (MarriageStatus != 0)
                                lisTeam = lisTeam.Where(o => o.MaritalStatus == MarriageStatus);
                            if (ReadyPregnancybirth != 0)
                                lisTeam = lisTeam.Where(o => o.ConceiveStatus == ReadyPregnancybirth);
                            if (lisTeam.Count() > 1)
                            {
                                List<CreateClientTeamInfoesDto> PPteamlist = new List<CreateClientTeamInfoesDto>();

                                //根据危害因素岗位类别再次判断
                                if (!string.IsNullOrWhiteSpace(dataRow["危害因素（多个，用“|”隔开）"].ToString())
                                    && !string.IsNullOrWhiteSpace(dataRow["检查类型"].ToString()))
                                {
                                    var rhylist = dataRow["危害因素（多个，用“|”隔开）"].ToString().Split('|').ToList();
                                    var gwlb = dataRow["检查类型"].ToString();

                                    #region 判断接害因素是否相同
                                    var teampp = lisTeam.Where(p => p.CheckType == gwlb).ToList();
                                    foreach (var team in teampp)
                                    {
                                        if (!string.IsNullOrEmpty(team.RiskName))
                                        {
                                            var risklist = team.RiskName.Split('，').ToList();
                                            bool b1 = rhylist.All(risklist.Contains);
                                            bool b2 = risklist.All(rhylist.Contains);
                                            if (b1 && b2)
                                            {
                                                PPteamlist.Add(team);
                                            }
                                        }
                                    } 
                                    #endregion

                                    //lisTeam = lisTeam.Where(p=>p.RiskName== rhys && p.CheckType== gwlb);
                                }
                                if (PPteamlist.Count() == 1)
                                { dataRow[CustomerTemplate.TeamCode] = PPteamlist.Select(o => o.TeamBM).FirstOrDefault(); }
                                else
                                {
                                    dataRow["导入状态"] = "无法确定哪个分组";
                                    dataRow["状态"] = 1;
                                }
                            }
                            else if (lisTeam.Count() == 0)
                            {
                                dataRow["导入状态"] = "未找到分组";
                                dataRow["状态"] = 1;
                            }
                            else
                                dataRow[CustomerTemplate.TeamCode] = lisTeam.Select(o => o.TeamBM).FirstOrDefault();
                        }
                        //编码不为空验证
                        else
                        {
                            var team = cteDto.ListClientTeam.FirstOrDefault(o => o.ClientRegId == ClientRegId && o.TeamBM == Convert.ToInt32(dataRow[CustomerTemplate.TeamCode]));
                            if (team == null)
                            {
                                dataRow["导入状态"] = "分组不存在";
                                dataRow["状态"] = 1;
                            }
                            else
                            {
                                if (team.MaxAge < Age || team.MinAge > Age)
                                {
                                    dataRow["导入状态"] = "年龄错误";
                                    dataRow["状态"] = 1;
                                }
                                if (dtSex != 0)
                                    if (team.Sex != (int)Sex.GenderNotSpecified)
                                    {
                                        if (team.Sex != dtSex)
                                        {
                                            dataRow["导入状态"] = "性别错误";
                                            dataRow["状态"] = 1;
                                        }
                                    }
                                //if (team.Sex != dtSex && team.Sex == (int)Sex.GenderNotSpecified)
                                //{
                                //    dataRow["导入状态"] = "性别错误";
                                //    dataRow["状态"] = 1;
                                //}
                                if (MarriageStatus != 0)
                                    if (team.MaritalStatus != 90 && team.MaritalStatus != MarriageStatus)
                                    {
                                        dataRow["导入状态"] = "婚姻状况错误";
                                        dataRow["状态"] = 1;
                                    }
                                if (ReadyPregnancybirth != 0)
                                    if (team.ConceiveStatus != 3 && team.ConceiveStatus != ReadyPregnancybirth)
                                    {
                                        dataRow["导入状态"] = "是否备孕错误";
                                        dataRow["状态"] = 1;
                                    }
                            }
                            dataRow["分组名称"] = team?.TeamName;
                        }
                    }
                    data.Rows.Add(dataRow);
                    if (dataRow["状态"].ToString() == "1")
                    {
                        DataRow ErrRow = Errordt.NewRow();
                        ErrRow.ItemArray = dataRow.ItemArray;
                        Errordt.Rows.Add(ErrRow);
                    }
                }               
                data.DefaultView.Sort = "状态 DESC";
            }
            if (Errordt.Rows.Count > 0)
            {
                var saveFileDialog = new System.Windows.Forms.SaveFileDialog();
                saveFileDialog.FileName = "异常列表";
                saveFileDialog.DefaultExt = "xls";
                saveFileDialog.Title = "导出Excel";
                saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";
                saveFileDialog.OverwritePrompt = false; //已存在文件是否覆盖提示
                var dialogResult = saveFileDialog.ShowDialog();
                if (dialogResult == System.Windows.Forms.DialogResult.OK)
                {

                    ExcelHelper ex = new ExcelHelper(saveFileDialog.FileName);
                    ex.DataTableToExcel(Errordt, "异常列表", true);
                }

            }
            return data;
            //}
            //catch (Exception ex)
            //{
            //    Console.WriteLine(@"Exception: " + ex.Message);
            //    alertInfo.Show(this, "提示!", ex.Message);
            //    return null;
            //}
        }

        /// <summary>
        /// 数据导入数据库
        /// </summary>
        public bool ImportDataSouce()
        {
            // 不判断 data 是否为空就直接筛选数据？
            // 你底下再判断 data 为空有什么用。
            // 如果 data 为空，上面就导致程序崩溃了。
            var data = gridControlImportExcel.DataSource as DataTable;
            if (data == null || data.Rows.Count == 0)
            {
                ShowMessageBoxInformation("没有名单可导入！");
                return false;
            }
            var suDr = data.Select("状态 = 0");   //正确数据
            var erDr = data.Select("状态 = 1");   //错误数据
            if (suDr == null || suDr.Count() == 0)
            {
                ShowMessageBoxInformation("没有正确名单可导入！");
                return false;
            }
            if (erDr != null && erDr.Count() > 0)
            {
                var IsTrue = MessageBox.Show("导入名单中有错误信息,是否继续导入,如果导入只导入正确信息。", "确认", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes;
                if (!IsTrue)
                    return false;
            }
            var Grouphelplis = CacheSimpleItemGroups;
            var dataList = new List<QueryCustomerRegDto>();
            AutoLoading(() =>
            {
                if (cteDto.ListClientTeamItem == null || cteDto.ListClientTeamItem.Count == 0)
                {
                    cteDto.ListClientTeamItem = _service.GetClientTeamRegItem(new SearchClientTeamInfoDto { ClientRegId = ClientRegId });
                }
                foreach (DataRow item in suDr)
                {
                    QueryCustomerRegDto dto = new QueryCustomerRegDto();
                    dto.ClientRegId = ClientRegId;
                    dto.CheckSate = (int)ProjectIState.Not;
                    dto.CostState = (int)PayerCatType.ClientCharge;
                    dto.RegisterState = (int)RegisterState.No;
                    dto.SendToConfirm = (int)SendToConfirm.No;
                    dto.SummSate = (int)SummSate.NotAlwaysCheck;
                    #region 人员信息
                    dto.Customer = new QueryCustomerDto();
                    var CustomerBM = item["体检号"].ToString();
                    //体检号在后台加载
                    //if (string.IsNullOrWhiteSpace(CustomerBM))
                    //    CustomerBM = _iIdNumber.CreateArchivesNumBM();
                    dto.CustomerBM = CustomerBM;
                    dto.Customer.ArchivesNum = CustomerBM;
                    dto.Customer.Name = item["姓名 *"].ToString();
                    var Sex = SexHelper.GetSexModels().Where(o => o.Display == item["性别 *"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (Sex != 0)
                        dto.Customer.Sex = Sex;
                    var Age = Convert.ToInt32(item["年龄 *"]);
                    if (Age != 0)
                        dto.Customer.Age = Age;
                    if (item["预检时间"] != null)
                    {
                        var bookingDate = Convert.ToDateTime(item["预检时间"]);
                        if (bookingDate != null)
                            dto.BookingDate = bookingDate;
                    }
                    var MarriageStatus = MarrySateHelper.GetMarrySateModels().Where(o => o.Display == item["婚姻状况"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (MarriageStatus != 0)
                        dto.Customer.MarriageStatus = MarriageStatus;
                    var ReadyPregnancybirth = BreedStateHelper.GetBreedStateModels().Where(o => o.Display == item["准备孕或育"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (ReadyPregnancybirth != 0)
                        dto.ReadyPregnancybirth = ReadyPregnancybirth;
                    dto.Customer.Mobile = item["移动电话"].ToString();
                    dto.Customer.Telephone = item["固定电话"].ToString();
                    dto.Customer.IDCardNo = item["身份证号"].ToString().Trim();

                    if (item["出生日期"] != null)
                    {
                        var birthday = Convert.ToDateTime(item["出生日期"]);
                        if (!string.IsNullOrWhiteSpace(birthday.ToString()))
                            dto.Customer.Birthday = birthday;
                    }
                    if (dto.Customer.IDCardNo != null && dto.Customer.IDCardNo != "")
                    {
                        var dataIDCard = VerificationHelper.GetByIdCard(dto.Customer.IDCardNo);
                        if (dataIDCard != null)
                        {
                            dto.Customer.Age = dataIDCard.Age;
                            dto.Customer.Sex = (int)dataIDCard.Sex;
                            dto.Customer.Birthday = dataIDCard.Birthday;
                        }
                    }
                    dto.Customer.Duty = item["职务"].ToString();
                    dto.Customer.Address = item["通讯地址"].ToString();
                    dto.Customer.PostgCode = item["邮政编码"].ToString();
                    dto.Customer.Email = item["电子邮件"].ToString();

                    // var tijianType = DefinedCacheHelper.GetBasicDictionarys(BasicDictionaryType.ExaminationType).FirstOrDefault(o => o.Text == item["体检类别"].ToString());
                    var tijianType = CacheBasicDictionarys.FirstOrDefault(p => p.Type == BasicDictionaryType.ExaminationType.ToString() && p.Text == item["体检类别"].ToString());
                    if (tijianType != null)
                    {
                        dto.PhysicalType = tijianType.Value;
                        if (item["体检类别"].ToString().Contains("食品"))
                        {
                            dto.JKZBM = iIDNumberAppService.CreateJKZBM();

                        }
                    }

                    // var CustomerType = DefinedCacheHelper.GetBasicDictionarys(BasicDictionaryType.CustomerType).FirstOrDefault(o => o.Text == item["客户类别"].ToString());
                    var CustomerType = CacheBasicDictionarys.FirstOrDefault(p => p.Type == BasicDictionaryType.CustomerType.ToString() && p.Text == item["客户类别"].ToString());

                    if (CustomerType != null)
                    {
                        dto.Customer.CustomerType = CustomerType.Value;
                        dto.CustomerType = CustomerType.Value;
                    }

                    dto.Customer.MedicalCard = item["医保卡号"].ToString();
                    dto.Customer.Qq = item["腾讯QQ"].ToString();
                    dto.Customer.Department = item["部门"].ToString();
                    dto.Customer.Nation = item["民族"].ToString();
                    dto.Customer.Remarks = item["备注"].ToString();
                    dto.Introducer = item["介绍人"].ToString();
                    dto.TypeWork = item["工种"].ToString();
                    dto.PersonnelCategoryId = cteDto.ClientRegDto.PersonnelCategoryId;
                    if (!dto.SummSate.HasValue)
                        dto.SummSate = (int)SummSate.NotAlwaysCheck;
                    //bug3333 【单位预约】添加分组页面勾选早餐等复选框在添加人员和导入人员时没有赋值
                    var selectTeaminfo = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode]));
                    if (selectTeaminfo != null)
                    {
                        if (selectTeaminfo.EmailStatus == 2)
                            dto.EmailReport = (int)MessageEmailState.Open;
                        else
                            dto.EmailReport = (int)MessageEmailState.Close;
                        if (selectTeaminfo.MessageStatus == 2)
                            dto.Message = (int)MessageEmailState.Open;
                        else
                            dto.Message = (int)MessageEmailState.Close;
                        dto.HaveBreakfast = selectTeaminfo.BreakfastStatus;
                        if (selectTeaminfo.HealthyMGStatus == 2)
                            dto.MailingReport = 1;
                        else
                            dto.MailingReport = 2;
                        dto.BlindSate = selectTeaminfo.BlindSate;
                        if (selectTeaminfo.TJType != null && dto.PhysicalType == null)
                        {
                            dto.PhysicalType = selectTeaminfo.TJType;
                        }
                    }
                    #endregion                 
                    #region 增加分组项目信息

                    //分组体检项目详细
                    var teminfo = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode]));
                    //职业健康相关
                    if (data.Columns.Contains("危害因素（多个，用“|”隔开）"))
                    {
                        if (item["危害因素（多个，用“|”隔开）"].ToString() == "")
                        {
                            dto.RiskS = teminfo.RiskName?.Replace("，", "|");
                        }
                        else
                        {
                            dto.RiskS = item["危害因素（多个，用“|”隔开）"].ToString();
                        }
                    }
                    if (data.Columns.Contains("检查类型"))
                    {
                        if (item["检查类型"].ToString() == "")
                        {
                            dto.PostState = teminfo.CheckType ?? "";
                        }
                        else
                        {
                            dto.PostState = item["检查类型"].ToString();
                        }
                    }
                    if (data.Columns.Contains("接害工龄"))
                    {
                        dto.InjuryAge = item["接害工龄"].ToString();
                    }
                    if (data.Columns.Contains("接害工龄单位"))
                    {
                        dto.InjuryAgeUnit = item["接害工龄单位"].ToString();
                        if (string.IsNullOrEmpty(dto.InjuryAgeUnit))
                        {
                            dto.InjuryAgeUnit = "年";
                        }
                    }
                    if (data.Columns.Contains("总工龄"))
                    {
                        dto.TotalWorkAge = item["总工龄"].ToString();
                    }
                    if (data.Columns.Contains("总工龄单位"))
                    {
                        dto.WorkAgeUnit = item["总工龄单位"].ToString();
                        if (string.IsNullOrEmpty(dto.WorkAgeUnit))
                        {
                            dto.WorkAgeUnit = "年";
                        }
                    }
                    if (data.Columns.Contains("车间"))
                    {
                        if (item["车间"].ToString() == "")
                        {
                            dto.WorkName = teminfo.WorkShop ?? "";
                        }
                        else
                        {
                            dto.WorkName = item["车间"].ToString();
                        }
                    }
                    if (data.Columns.Contains("工种"))
                    {
                        if (item["工种"].ToString() == "")
                        {
                            dto.TypeWork = teminfo.WorkType ?? "";
                        }
                        else
                        {
                            dto.TypeWork = item["工种"].ToString();
                        }
                    }

                    var teamId = teminfo.Id;
                    if (!dto.PhysicalType.HasValue)
                    {
                        dto.PhysicalType = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode])).TJType;
                    }
                    var ClientTeamRegList = cteDto.ListClientTeamItem.Where(o => o.ClientTeamInfoId == teamId);

                    if (ClientTeamRegList != null)
                    {
                        dto.ClientInfoId = cteDto.ClientRegDto.ClientInfo_Id;
                        dto.ClientType = cteDto.ClientRegDto.ClientSate.ToString();
                        dto.ClientTeamInfo_Id = ClientTeamRegList.Select(o => o.ClientTeamInfoId).FirstOrDefault();
                        dto.ItemSuitBMId = ClientTeamRegList.Select(o => o.ItemSuitId).FirstOrDefault();
                        dto.ItemSuitName = ClientTeamRegList.Select(o => o.ItemSuitName).FirstOrDefault();

                        Departments = CacheDepartmentDtos;
                        dto.CustomerItemGroup = new List<TjlCustomerItemGroupDto>();
                        dto.ClientTeamRegitemInfo = ClientTeamRegList.ToList();
                    }
                    #endregion

                    //增加数据库

                    dataList.Add(dto);
                    if (dataList.Count > 50)
                    {
                        var retDatals = _service.RegCustomer(dataList);
                        dataList.Clear();
                    }
                }
                var retData = _service.RegCustomer(dataList);
                if (retData.Count > 0)
                {
                    ShowMessageSucceed("导入成功！");
                }

                //var retData = customerSvr.RegCustomer(dataList);
            });
            return true;
        }
        /// <summary>
        /// 数据导入数据库
        /// </summary>
        public bool ImportDataSouceNew()
        {
            // 不判断 data 是否为空就直接筛选数据？
            // 你底下再判断 data 为空有什么用。
            // 如果 data 为空，上面就导致程序崩溃了。
            var data = gridControlImportExcel.DataSource as DataTable;
            if (data == null || data.Rows.Count == 0)
            {
                ShowMessageBoxInformation("没有名单可导入！");
                return false;
            }
            var suDr = data.Select("状态 = 0");   //正确数据
            var erDr = data.Select("状态 = 1");   //错误数据
            if (suDr == null || suDr.Count() == 0)
            {
                ShowMessageBoxInformation("没有正确名单可导入！");
                return false;
            }
            if (erDr != null && erDr.Count() > 0)
            {
                var IsTrue = MessageBox.Show("导入名单中有错误信息,是否继续导入,如果导入只导入正确信息。", "确认", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes;
                if (!IsTrue)
                    return false;
            }
            #region 判断身份证号重复
            StringBuilder sb = new StringBuilder();

            if (IsDuplicateFieldValue(suDr, "身份证号", sb))
            {
               // Msg.Warning("表格中存在重复的订单号！\r\n" + sb.ToString());
                DialogResult dr = XtraMessageBox.Show("导入数据身份证号重复是否继续导入？\r\n"
                    + sb.ToString()
                    , "确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (dr == DialogResult.Cancel)
                {
                    return false;
                }
            }
            #endregion
            var Grouphelplis = CacheSimpleItemGroups;
            var dataList = new List<ClientCustomerRegDto>();
            AutoLoading(() =>
            {
                if (cteDto.ListClientTeamItem == null || cteDto.ListClientTeamItem.Count == 0)
                {
                    cteDto.ListClientTeamItem = _service.GetClientTeamRegItem(new SearchClientTeamInfoDto { ClientRegId = ClientRegId });
                }
               
                foreach (DataRow item in suDr)
                {
                    ClientCustomerRegDto dto = new ClientCustomerRegDto();
                    dto.ClientRegId = ClientRegId;
                    dto.CheckSate = (int)ProjectIState.Not;
                    dto.CostState = (int)PayerCatType.ClientCharge;
                    dto.RegisterState = (int)RegisterState.No;
                    dto.SendToConfirm = (int)SendToConfirm.No;
                    dto.SummSate = (int)SummSate.NotAlwaysCheck;
                    try
                    {
                        if (!string.IsNullOrWhiteSpace(item["监测类型"].ToString()))
                        {
                            dto.MonitorTypeID = (Guid)showOccDictionarys.First<ShowOccDictionary>(it => it.Text == item["监测类型"].ToString()).Id;
                        }
                    }
                    catch (Exception)
                    {
                        dto.MonitorTypeID = null;
                    }


                    #region 人员信息
                    dto.Customer = new QueryCustomerDto();
                    var CustomerBM = item["体检号"].ToString();
                    //if (string.IsNullOrWhiteSpace(CustomerBM))
                    //    CustomerBM = _iIdNumber.CreateArchivesNumBM();
                    dto.CustomerBM = CustomerBM;
                    dto.Customer.ArchivesNum = CustomerBM;
                    dto.Customer.Name = item["姓名 *"].ToString();
                    var Sex = SexHelper.GetSexModels().Where(o => o.Display == item["性别 *"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (Sex != 0)
                        dto.Customer.Sex = Sex;
                    var Age = Convert.ToInt32(item["年龄 *"]);
                    //if (Age != 0)
                        dto.Customer.Age = Age;
                    if (item["预检时间"].ToString() != null && item["预检时间"].ToString() != "")
                    {
                        var bookingDate = Convert.ToDateTime(item["预检时间"]);
                        if (bookingDate != null)
                        {
                            dto.BookingDate = bookingDate;
                            //dto.AppointmentTime = bookingDate;
                        }
                    }
                    if (item["预检时间"] != null && item["预检时间"].ToString() != "")
                    {
                        dto.BookingStatus = 1;
                    }
                    else
                    {
                        dto.BookingStatus = 0;
                    }
                    var MarriageStatus = MarrySateHelper.GetMarrySateModels().Where(o => o.Display == item["婚姻状况"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (MarriageStatus != 0)
                        dto.Customer.MarriageStatus = MarriageStatus;
                    var ReadyPregnancybirth = BreedStateHelper.GetBreedStateModels().Where(o => o.Display == item["准备孕或育"].ToString()).Select(o => o.Id).FirstOrDefault();
                    if (ReadyPregnancybirth != 0)
                        dto.ReadyPregnancybirth = ReadyPregnancybirth;
                    dto.Customer.Mobile = item["移动电话"].ToString();
                    dto.Customer.Telephone = item["固定电话"].ToString();
                    dto.Customer.IDCardNo = item["身份证号"].ToString().Trim();

                    if (item["出生日期"] != null)
                    {
                        var birthday = Convert.ToDateTime(item["出生日期"]);
                        if (!string.IsNullOrWhiteSpace(birthday.ToString()))
                            dto.Customer.Birthday = birthday;
                    }
                    if (dto.Customer.IDCardNo != null && dto.Customer.IDCardNo != "")
                    {
                        var dataIDCard = VerificationHelper.GetByIdCard(dto.Customer.IDCardNo);
                        if (dataIDCard != null)
                        {
                            dto.Customer.Age = dataIDCard.Age;
                            dto.Customer.Sex = (int)dataIDCard.Sex;
                            dto.Customer.Birthday = dataIDCard.Birthday;
                        }
                    }
                    dto.Customer.Duty = item["职务"].ToString();
                    dto.Customer.Address = item["通讯地址"].ToString();
                    dto.Customer.PostgCode = item["邮政编码"].ToString();
                    dto.Customer.Email = item["电子邮件"].ToString();

                    //var tijianType = DefinedCacheHelper.GetBasicDictionarys(BasicDictionaryType.ExaminationType).FirstOrDefault(o => o.Text == item["体检类别"].ToString());
                    var tijianType = CacheBasicDictionarys.FirstOrDefault(p => p.Type == BasicDictionaryType.ExaminationType.ToString() && p.Text == item["体检类别"].ToString());

                    if (tijianType != null)
                    {
                        dto.PhysicalType = tijianType.Value;
                        if (item["体检类别"].ToString().Contains("食品"))
                        {
                            dto.JKZBM = iIDNumberAppService.CreateJKZBM();
                        }
                    }

                    //var CustomerType = DefinedCacheHelper.GetBasicDictionarys(BasicDictionaryType.CustomerType).FirstOrDefault(o => o.Text == item["客户类别"].ToString());
                    var CustomerType = CacheBasicDictionarys.FirstOrDefault(p => p.Type == BasicDictionaryType.CustomerType.ToString() && p.Text == item["客户类别"].ToString());
                    if (CustomerType != null)
                    {
                        dto.Customer.CustomerType = CustomerType.Value;
                        dto.CustomerType = CustomerType.Value;
                    }

                    dto.Customer.MedicalCard = item["医保卡号"].ToString();
                    dto.Customer.Qq = item["腾讯QQ"].ToString();
                    if (data.Columns.Contains("工号") && item["工号"] != null)
                    {
                        dto.Customer.WorkNumber = item["工号"].ToString();
                    }
                    if (data.Columns.Contains("证件类型") && item["证件类型"] != null)
                    {
                        //var CardType = DefinedCacheHelper.GetBasicDictionary().Where(o => o.Type == BasicDictionaryType.CertificateType.ToString()).FirstOrDefault(o => o.Text == item["证件类型"].ToString());
                        var CardType = CacheBasicDictionarys.FirstOrDefault(p => p.Type == BasicDictionaryType.CertificateType.ToString() && p.Text == item["证件类型"].ToString());

                        dto.Customer.IDCardType = CardType?.Value;
                   }
                    //dto.InfoSource = 1;

                   // dto.Customer.Department = item["部门"].ToString();
                    dto.Department = item["部门"].ToString();
                    dto.Customer.Nation = item["民族"].ToString();
                    dto.Customer.Remarks = item["备注"].ToString();
                    if (data.Columns.Contains("预约备注") && item["预约备注"] != null)
                    {
                        dto.Remarks = item["预约备注"].ToString();
                    }
                    dto.Introducer = item["介绍人"].ToString();
                    dto.TypeWork = item["工种"].ToString();
                    dto.PersonnelCategoryId = cteDto.ClientRegDto.PersonnelCategoryId;
                    if (!dto.SummSate.HasValue)
                        dto.SummSate = (int)SummSate.NotAlwaysCheck;
                    //bug3333 【单位预约】添加分组页面勾选早餐等复选框在添加人员和导入人员时没有赋值
                    var selectTeaminfo = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode]));
                    if (selectTeaminfo != null)
                    {
                        if (selectTeaminfo.EmailStatus == 2)
                            dto.EmailReport = (int)MessageEmailState.Open;
                        else
                            dto.EmailReport = (int)MessageEmailState.Close;
                        if (selectTeaminfo.MessageStatus == 2)
                            dto.Message = (int)MessageEmailState.Open;
                        else
                            dto.Message = (int)MessageEmailState.Close;
                        dto.HaveBreakfast = selectTeaminfo.BreakfastStatus;
                        if (selectTeaminfo.HealthyMGStatus == 2)
                            dto.MailingReport = 1;
                        else
                            dto.MailingReport = 2;
                        dto.BlindSate = selectTeaminfo.BlindSate;
                        if (selectTeaminfo.TJType != null && dto.PhysicalType == null)
                        {
                            dto.PhysicalType = selectTeaminfo.TJType;
                        }
                    }
                    #endregion                 
                    #region 增加分组项目信息

                    //分组体检项目详细
                    var teminfo = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode]));
                    //职业健康相关
                    if (data.Columns.Contains("危害因素（多个，用“|”隔开）"))
                    {
                        if (item["危害因素（多个，用“|”隔开）"].ToString() == "")
                        {
                            dto.RiskS = teminfo.RiskName?.Replace("，", "|");
                        }
                        else
                        {
                            dto.RiskS = item["危害因素（多个，用“|”隔开）"].ToString();
                        }
                    }
                    if (data.Columns.Contains("检查类型"))
                    {
                        if (item["检查类型"].ToString() == "")
                        {
                            dto.PostState = teminfo.CheckType ?? "";
                        }
                        else
                        {
                            dto.PostState = item["检查类型"].ToString();
                        }
                    }
                    if (data.Columns.Contains("接害工龄"))
                    {
                        dto.InjuryAge = item["接害工龄"].ToString().Replace("年","").Replace("月","").Replace("日","");
                    }
                    if (data.Columns.Contains("接害工龄单位"))
                    {
                        dto.InjuryAgeUnit = item["接害工龄单位"].ToString();
                        if (string.IsNullOrEmpty(dto.InjuryAgeUnit))
                        {
                            dto.InjuryAgeUnit = "年";
                        }
                    }
                    if (data.Columns.Contains("总工龄"))
                    {
                        dto.TotalWorkAge = item["总工龄"].ToString().Replace("年", "").Replace("月", "").Replace("日", "");
                    }
                    if (data.Columns.Contains("总工龄单位"))
                    {
                        dto.WorkAgeUnit = item["总工龄单位"].ToString();
                        if (string.IsNullOrEmpty(dto.WorkAgeUnit))
                        {
                            dto.WorkAgeUnit = "年";
                        }
                    }
                    if (data.Columns.Contains("车间"))
                    {
                        if (item["车间"].ToString() == "")
                        {
                            dto.WorkName = teminfo.WorkShop ?? "";
                        }
                        else
                        {
                            dto.WorkName = item["车间"].ToString();
                        }
                    }
                    if (data.Columns.Contains("工种"))
                    {
                        if (item["工种"].ToString() == "")
                        {
                            dto.TypeWork = teminfo.WorkType ?? "";
                        }
                        else
                        {
                            dto.TypeWork = item["工种"].ToString();
                        }
                    }
                    #region 接害开始时间
                    if (data.Columns.Contains("接害开始时间") && item["接害开始时间"].ToString() != null && item["接害开始时间"].ToString() != "" 
                    && DateTime.TryParse(item["接害开始时间"].ToString(),out DateTime dtStar))
                    {

                        if (dtStar != null)
                        {
                            dto.StarTime = dtStar;
                        
                        }
                    }
                    if (item["预检时间"] != null && item["预检时间"].ToString() != "")
                    {
                        dto.BookingStatus = 1;
                    }
                    #endregion
                    if (data.Columns.Contains("其他工种"))
                    {
                        dto.OtherTypeWork = item["其他工种"].ToString();
                    }
                    if (data.Columns.Contains("用工单位名称"))
                    {
                        dto.employerEnterpriseName  = item["用工单位名称"].ToString();
                    }
                    if (data.Columns.Contains("用工单位机构代码"))
                    {
                        dto.employerCreditCode = item["用工单位机构代码"].ToString();
                    }
                    var teamId = teminfo.Id;
                    if (!dto.PhysicalType.HasValue)
                    {
                        dto.PhysicalType = cteDto.ListClientTeam.FirstOrDefault(o => o.TeamBM == Convert.ToInt32(item[CustomerTemplate.TeamCode])).TJType;
                    }
                    var ClientTeamRegList = cteDto.ListClientTeamItem.Where(o => o.ClientTeamInfoId == teamId);
                  
                    if (ClientTeamRegList != null)
                    {
                        dto.ClientInfoId = cteDto.ClientRegDto.ClientInfo_Id;
                        dto.ClientType = cteDto.ClientRegDto.ClientSate.ToString();
                        dto.ClientTeamInfo_Id = ClientTeamRegList.Select(o => o.ClientTeamInfoId).FirstOrDefault();
                        dto.ItemSuitBMId = ClientTeamRegList.Select(o => o.ItemSuitId).FirstOrDefault();
                        dto.ItemSuitName = ClientTeamRegList.Select(o => o.ItemSuitName).FirstOrDefault();
                        if (!dto.ClientTeamInfo_Id.HasValue)
                        {
                            MessageBox.Show(teminfo.TeamName +"无分组项");
                            return;
                        }
                        Departments = CacheDepartmentDtos;

                    }
                    else
                    {
                    }
                    #endregion

                    //增加数据库

                    dataList.Add(dto);
                    if (dataList.Count > 1000)
                    {
                        var retDatals = _service.RegClientCustomer(dataList);
                        dataList.Clear();
                    }
                }
                var retData = _service.RegClientCustomer(dataList);
                //var retData = customerSvr.RegCustomer(dataList);
            });
            return true;
        }
        /// <summary>
        /// 检查Datatable表指定字段的值是否重复
        /// </summary>
        /// <param name="dtCheck">需要检查的表</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="sb">需要返回的结果信息</param>
        /// <returns></returns>
        public  bool IsDuplicateFieldValue(DataRow[] rowlist, string fieldName, StringBuilder sb = null)
        {
            DataTable dtCheck = ToDataTable(rowlist);
            if (sb != null)
            {
                DataRow[] rows; string value;
                foreach (DataRow R in dtCheck.Rows)
                {
                    value = R[fieldName] == DBNull.Value ? "" : R[fieldName].ToString();
                    rows = dtCheck.Select(string.Format("{0}='{1}'", fieldName, value));
                    if (rows.Length > 1) sb.Append(sb.Length == 0 ? value : "," + value);
                }
                return sb.Length > 0;
            }
            else
            {
                //使用DataView对象获取唯一值
                DataView dv = new DataView(dtCheck);
                return dv.Count != dv.ToTable(true, fieldName).Rows.Count;
            }
        }
        public DataTable ToDataTable(DataRow[] rows)
        {
            if (rows == null || rows.Length == 0) return null;
            DataTable tmp = rows[0].Table.Clone();  // 复制DataRow的表结构  
            foreach (DataRow row in rows)
                tmp.Rows.Add(row.ItemArray);  // 将DataRow添加到DataTable中  
            return tmp;
        }
        private void FrmImportExcel_Load(object sender, EventArgs e)
        {
          
        }
        private bool getFZState()
        {
            bool isFZState = false;
            if (ClientRegId != Guid.Empty)
            {
                var FZSt = _chargeAppService.GetZFState(new EntityDto<Guid> { Id = ClientRegId });
                if (FZSt == 1)
                {
                    return true;
                }
            }
            return isFZState;

        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
        }

        private void FrmImportExcel_Shown(object sender, EventArgs e)
        {
            CacheBasicDictionarys = DefinedCacheHelper.GetBasicDictionary();
            CacheSimpleItemGroups = DefinedCacheHelper.GetItemGroups();
            CacheDepartmentDtos = DefinedCacheHelper.GetDepartments();
            showOccDictionarys = _IOccDisProposalNewAppService.GetAll(new ShowOccDictionary()
            {
                Type = "JCLX",
                IsActive = 1
            });
        }
    }

    #endregion
    public class ExcelCusDto
    {

        public virtual int TeamBM { get; set; }

        /// <summary>
        /// 分组名称
        /// </summary>
        public virtual string 姓名 { get; set; }
        public virtual string 性别 { get; set; }
        public virtual int? 年龄 { get; set; }
        public virtual string 婚姻状况 { get; set; }

        public virtual string 分组编码 { get; set; }
        public virtual string 体检号 { get; set; }
        public virtual string 移动电话 { get; set; }
        public virtual string 身份证号 { get; set; }
        public virtual string 出生日期 { get; set; }
        public virtual string 车间 { get; set; }
        public virtual string 工种 { get; set; }
        public virtual string 接害工龄 { get; set; }
        public virtual string 总工龄 { get; set; }
        public virtual string 危害因素 { get; set; }
        public virtual string 体检类别 { get; set; }
        public virtual string 工号 { get; set; }
        public virtual string 部门 { get; set; }

        public virtual string 职务 { get; set; }

        public virtual string 医保卡号 { get; set; }

        public virtual string 预检时间 { get; set; }
        public virtual string 流水号 { get; set; }
        public virtual string 民族 { get; set; }
        public virtual string 准备孕或育 { get; set; }
        public virtual string 备注 { get; set; }
        public virtual string 预约备注 { get; set; }

        public virtual string 介绍人 { get; set; }
        public virtual string 固定电话 { get; set; }

        public virtual string 检查类型 { get; set; }

        public virtual string 接害工龄单位 { get; set; }

        public virtual string 总工龄单位 { get; set; }

        public virtual string 客户类别 { get; set; }

        public virtual string 通讯地址 { get; set; }

        public virtual string 邮政编码 { get; set; }
        public virtual string 电子邮件 { get; set; }
        public virtual string 腾讯QQ { get; set; }

    }
}